During the development phase of the project, developers can test the SSIS package execution by running the package from BIDS.
Open BIDS, configure a new package ----------------->
Press Alt+Ctrl+L for solution explorer ----------------->
Select the package ----------------->
Right-click and select execute package option
Command Line utility
Using the DTEXEC command-line utility can execute an SSIS package that is stored in the File system or SQL Server.
For example: DTEXEC.EXE /F “C:packagescheck points .dtsx”
Do you want to Master MSBI? Then enrol in "MSBI Training" This course will help you to master MSBI
Using the execute package utility graphical interphase can execute an SSIS package that is stored in the file system or SQL server.
In the command line type DTEXEC UI.EXE and press enter which will open up execute package utility editor.
In executing package utility editor select the general tab,
Package Source – File System
Package – Click Browser ----------------->
Select any package from the list and click open ----------------->
Click execute to execute the linked/ embedded package.
(or)
The execute package utility is also used when you execute the SSIS package from the integration services node in the SQL server management studio.
Open SQL Server Management Studio ----------------->
Connect to integration services ----------------->
Expand stored packages ----------------->
Expand file system
Select file system and right-click, Select import
Package editor
Package location – file system
Package – click browse ----------------->
Select any package from
The list and click open ----------------->
Package name - place cursor ----------------->
Click ok ----------------->
Select imported package ----------------->
Right-click and select run package ----------------->
In execute, package utility editor click execute
Open SQL server management studio ----------------->
Connect to database engine ----------------->
Select SQL server agent
Note: Ensure that SQL server agent service is start mode
Select Jobs ----------------->
Right-click and select a new job
Provide Job Name as Load for each loop container ----------------->
Select Steps page ----------------->
Click new ----------------->
Step Name - Load for each loop container
Type - SQL Server Integration Service Package ----------------->
In General Tab,
Package Source - File System
Package – click Browse ----------------->
Select any package from the list of package ----------------->
Click open ----------------->
Click ok ----------------->
Select Schedule package ----------------->
Click new ----------------->
Provide the job Name as Load For each loop –Container ----------------->
Schedule Type – Recurring ----------------->
Set the Frequency (When to start the execution of the specified package) ----------------->
Click ok twice
It erased to compare the data from source to reference dataset. Using a Reference dataset using simple equally joint.
Note: While implementing data warehousing the reference data must be dimension table.
Steps to implement Look Up Transformation:
Open Business Intelligence development studio ----------------->
Create a new package and rename it as lookup .dtsx ----------------->
dn control flow drag and drop and flow task ----------------->
dn data flow, drag and drop OLEDB Source ----------------->
Double click on OLEDB Source to edit it ----------------->
Provide Connection Manager if exists and select
Production .product category ----------------->
Select columns and click ok
Drag and drop lookup Transformation and make
A connection from the source to lookup ----------------->
Provide connection manager and select production
.product subcategory ----------------->
Click configures error output and set Redirect Rows under error header ----------------->
Click ok ----------------->
Click ok ----------------->
Drag and drop OLEDB destination ----------------->
Make a connection from Lookup to destination ----------------->
Double click on OLEDB definition to configure it ----------------->
Provide destination connection manager and ----------------->
Click new to create a new destination table ----------------->
And rename the table as Matched data ----------------->
Click ok ----------------->
Select Mappings ----------------->
Drag and drop OLEDB destination to capture
UN matched records from source to reference dataset. ----------------->
Make a connection from Look Up to OLEDB destination
Using error output (Read data flow path) ----------------->
Click ok in lookup error output editor ----------------->
Double click on a destination to configure it ----------------->
Provide destination connection Manager and click
New to create destination table and rename it as
UN matched-records ----------------->
Select mapping and click ok ----------------->
Execute package
Note:
Scenario: TO the Dynamic flat file destination
In control flow drag a drop data flow Task ----------------->
Define the following variables with respect to the package.
| Name | Data type | value |
| Uv source path | string | D:ssis packagespackages |
| Uv File Name | string | vendor |
In Data flow drag and drop OLEDB source
Double click on OLEDB source to edit it ----------------->
Provide a Source connection manager and select production. Production sub-category
From drop-down list ----------------->
Select columns ----------------->
Drag and drop flat file destination and make a connection from OLEDB Source to flat-file ----------------->
Double click on the flat file to edit it ----------------->
Click new to configure new flat file connection Manager ----------------->
Select Delimited flat-file format ----------------->
Click ok ----------------->
Connection Manager Name - Dynamic vendor flat file
Description – Dynamic vendor flat file
File Name – Type the following path and file with an extension that is not available at the destination.
D:ssis PackagePackagevendor.txt ----------------->
Select columns page ----------------->
Click ok ----------------->
Select Mappings page ----------------->
Click ok, In Connection Manager, Select dynamic vendor flat file ----------------->
Press F4 for properties ----------------->
Expression – click browse
Select connection string and click in expression builder, provide the following expression which create a new flat file dynamically
@ [user:: Uv source path]+””+@[user::UV File Path] + “-”+(DT_WSTR,10)(DT_DBDATE)
@ [System::start Time] +’.txt’ ----------------->
Click ok twice ----------------->
Close property window ----------------->
Execute package
Script task is used to design custom interphases.
Create a text file on every corresponding month dynamically using script task.
Define the following variables type string
Uv Source path: D:ssispackagespackages
Uv file name: Product category Details on
Uv Full path: ----------------->
Drag and Drop script task ----------------->
Double click on the script task to editor configure it. ----------------->
Select Script from the left panel and set, ----------------->
Read-Only Variables – Uv Source path, Uv File Name
Read-write variable – Uv Full path ----------------->
Click Design Script, Opens MS Virtual Studio for Applications IDE, ----------------->
Provide the following VB.Net Script to create dynamic text file the specified name.
Dim s Source path As String
Dim s File name As String
Dim s Full path As String
‘D:ssis package.Package
S Source path = Dts. variables (“Uv source path”).value.Tostring
‘Product category Details on sFile Name = Dts.variables(“Uv File Name”).Value.Tostring
‘D:ssis packagespackagesproduct category Details on-
sFull path = ssource path + sFile Name +”_”+Month
(Now ()) ,Tostring() +Year(Now()).Tostring()+.”txt”
Dts.variables(“Uv Fullpath”).value = sFullpath.To string() ----------------->
Select Debug Menu and Select build to build the above Scripting ----------------->
Select file menu and Select close and return ----------------->
Click ok ----------------->
Drag and drop data flow task and make a connection from script task to data flow task ----------------->
In Data Flow Drag and Drop OLEDB Source ----------------->
Double click on OLEDB Source to configure to ----------------->
Provide connection Manager if exits ----------------->
Select Product category table ----------------->
Select columns from left pane and click ok ----------------->
Drag and drop flat file destination and make a connection from OLEDB source to flat file destination ----------------->
Double click on flat file destination ----------------->
Click new to create new connection Manager ----------------->
Select Delimited flat file format ----------------->
Click ok ----------------->
Provide flat file connection manager name and description if any ----------------->
Type the following path ----------------->
D:ssis packagepackageproduct category Detailson.txt ----------------->
Select columns from left panel ----------------->
Click ok ----------------->
Select mapping from left panel ----------------->
Click ok ----------------->
To connection manager select flat file connection manager ----------------->
Press F4 for properties and set, expression - click Browse ----------------->
Select connection string and click browse to build the expression in expression builder ----------------->
Expand Variables ----------------->
Drag and Drop User:: Uv Full path in to expression ----------------->
Section, i.e @ [user:: Uv Full path] ----------------->
Click ok twice ----------------->
Close properties window ----------------->
Execute the package
Providing Security for SSIS Package:
The protection level is in the SSIS package that is used to specify how sensitive information is saved with
In the package and also whether to encrypt the package or sensitive portions of the package.
Example1 the sensitive information would be password to the Database.
Steps to Configure protection level in SSIS
Open Business Intelligence Development Studio
Create OLEDB connect with server authentication and provide
Design package
Select package in control flow, right-click
Select properties,
Protection level – Don’t save Sensitive
When you Specified Don’t Save Sensitive as the protection level, any sensitive information
Is not written to the package XML file when you save the package. This could be useful when
You want to make sure that anything sensitive is excluded from the package before sending
It to someone. After saving the package with this sending, open the OLEDB Connection Manager,
The Password is black even though save my password checkbox is checked.
Encrypt Sensitive with User Key encrypt Sensitive information based on the credentials of the
The user who created the Package.
There is a limitation with this setting if another user (a different user than the one who created
The package and saved it) open the package the following error will be displayed, error loading
Encrypts Sensitive with user key; failed to encrypt protection level XML load (dts. Password).
The Encrypt Sensitive with password setting require a password in the package and that
Password will be used to encrypt and decrypt the sensitive information in the package. To fill in the
Package password clicks on the button in the package password field of the package and provide
Password and confirm password. When you open a package with this setting you will be prompted
To enter the password.
Note: The Encrypt Sensitive with Password Setting for the Production level property overcomes
The limitation of the encrypt Sensitive with user key setting by allowing any user to open the package
As long as they have to password.
The Encrypt All with password Setting used to encrypt the entire content Of the SSIS package with the specified password. You specify the package Password in the Package
Property, Same as Encrypt Sensitive with password settings. After saving the package you can
View the package XML code that is already encrypted in between encrypted data tags in the package XML.
The Encrypt All with User Key Setting is used to encrypt the entire
Contents of the SSIS package by using User Key this means that only the user who created the package
Will be able to open it, view or modify it, and run it.
The server storage Settings for the Production level property allows the package
To return all Sensitive information when you are saving the package to the SQL server. SSIS packages Saved to SQL Server use the MS DB Database.
The presentation of the data is required for easy analysis turning columns into rows
And rows into columns are another way of presentation of data. So that the end-user can understand
It easily.
A process of turning columns to Rows is known as Unpivot.
Prepare the following Excel Sheet
| Year | Category | Jan | Feb | March | April |
| 2008 | Bikes | 100 | 200 | 300 | 400 |
| 2008 | Accessories | 200 | 270 | 300 | 320 |
| 2009 | Components | 100 | 120 | 300 | 150 |
| 2009 | Phones and components | 400 | 800 | 400 | 300 |
Open Business Intelligence Development Studio
Create a new package and rename it as UN pivot. dtsx
In control flow drag and drop Data Flow Task
In Data Flow drag and drop Excel source
Double click on Excel source to configure it ----------------->
Click ok ----------------->
Click Browse ----------------->
Select Unpirot.xls file and click open ----------------->
Click ok ----------------->
Select sheet 1 from the drop-down list ----------------->
Select columns and click ok ----------------->
Drag and drop Unpivot transformation and make a connection from the excel source to Unpivot ----------------->
Double click on Unpivot ----------------->
Select the below columns to unpivot then, Jan, Feb, March, April, May, June ----------------->
Rename pivot key value column Name as – Months ----------------->
Specify sales amount as a Derived on Destination column for all the selected pivoted Key values or input columns ----------------->
Click ok ----------------->
Make sure that the Excel source file is closed. ----------------->
Drag and drop Excel destination ----------------->
Make a connection from Unpivot to Excel destination ----------------->
Double click on Excel destination to configure it ----------------->
Click New to create new destination excel sheet and rename it as Unpivot data ----------------->
Click ok ----------------->
Select Mappings ----------------->
Execute package
A process of turning rows into columns is known as a pivot
Prepare the following Excel sheet for source data.
| Year | Quarter | Sales Amount |
| 2009 | Q1 | 100 |
| 2009 | Q2 | 200 |
| 2009 | Q3 | 300 |
| 2009 | Q4 | 400 |
Rename the Excel Pivot.xls ----------------->
Open Business Intelligence Development Studio ----------------->
Create a new package and rename it as Pivot. dtsx ----------------->
In control flow drag and drop data flow task. ----------------->
In Data Flow Drag and Drop Excel source ----------------->
Double click on Excel source to configure it ----------------->
Click New ----------------->
Click Browse ----------------->
Select pivot .xls and click open ----------------->
Click ok ----------------->
Select sheet 1 from the drop-down list ----------------->
Drag and Drop pivot transformation and make a connection from Excel source to Pivot ----------------->
Double click on Pivot ----------------->
Select Input columns tab and check all input columns ----------------->
Select input-output properties tab ----------------->
Expand pivot default output ----------------->
Expand Input column ----------------->
Select Year and Set, pivot usage – 1 ----------------->
Select Quarter input column and Set, pivot usage – 2
Select Sales Amount input column and set, pivot usage – 3
Pivot Usage tells ssis how to treat the data what its role during the
Transformation process
0 – The column is not pivoted1
All input rows with the same set key are considered into 1 output row.
Export pivot default output and create the following columns by click the add columns button Year Q1, Q2, Q3, Q4 ----------------->
Copy or Note the Lineage ID of input column year
Select year output column and set,
Pivot Key-value – Year
Source column – 58(Lineage ID of year input column)
Select Q1 and set,
Pivot Key-value – Q1
Source column – 64
Select Q2 and set,
Pivot Key value – Q2
Source column – 64
And follow the same process for Q3 and Q4 output columns ----------------->
Click Refresh ----------------->
Click ok ----------------->
Drag and Drop Excel destination ----------------->
Double click on Excel destination to edit it ----------------->
Provide Excel connection Manager, ----------------->
Click New to create a new table (sheet) and name it as pivot data ----------------->
Click ok twice ----------------->
Execute package
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
